import { getConnection, isDatabaseConnected } from '../config/database.js';
import { PathMappingService } from './PathMappingService.js';
import fs from 'fs/promises';
import path from 'path';

export interface Project {
  id: string;
  name: string;
  path: string;
  description?: string;
  createdAt: string;
  updatedAt: string;
}

const PROJECTS_FILE = path.join(process.cwd(), 'data', 'projects.json');

/**
 * 数据库项目服务类
 */
export class DatabaseProjectService {
  /**
   * 获取所有项目
   */
  static async getAllProjects(): Promise<Project[]> {
    const dbConnected = isDatabaseConnected();
    
    console.log('🔧 DatabaseProjectService.getAllProjects - 数据库连接状态:', dbConnected);
    
    let projects: Project[];
    if (dbConnected) {
      console.log('📊 使用数据库读取项目');
      projects = await this.getAllFromDatabase();
      console.log('📊 数据库读取项目结果:', JSON.stringify(projects, null, 2));
    } else {
      console.log('📁 使用文件读取项目');
      projects = await this.getAllFromFile();
      console.log('📁 文件读取项目结果:', JSON.stringify(projects, null, 2));
    }
    
    // 在Docker环境中应用路径映射
    if (PathMappingService.isRunningInDocker()) {
      console.log('🐳 Docker环境检测到，应用路径映射');
      projects = PathMappingService.mapProjectPaths(projects);
      console.log('🐳 路径映射后的项目:', JSON.stringify(projects, null, 2));
    }
    
    return projects;
  }

  /**
   * 添加项目
   */
  static async addProject(project: Omit<Project, 'id' | 'createdAt' | 'updatedAt'>): Promise<Project> {
    const now = new Date();
    const mysqlDateTime = now.toISOString().slice(0, 19).replace('T', ' ');
    
    const newProject: Project = {
      id: Date.now().toString(),
      ...project,
      createdAt: mysqlDateTime,
      updatedAt: mysqlDateTime
    };

    if (isDatabaseConnected()) {
      await this.addToDatabase(newProject);
    } else {
      await this.addToFile(newProject);
    }

    return newProject;
  }

  /**
   * 删除项目
   */
  static async deleteProject(id: string): Promise<void> {
    if (isDatabaseConnected()) {
      await this.deleteFromDatabase(id);
    } else {
      await this.deleteFromFile(id);
    }
  }

  /**
   * 更新项目
   */
  static async updateProject(id: string, updates: Partial<Omit<Project, 'id' | 'createdAt'>>): Promise<Project | null> {
    if (isDatabaseConnected()) {
      return await this.updateInDatabase(id, updates);
    } else {
      return await this.updateInFile(id, updates);
    }
  }

  // 数据库操作方法
  private static async getAllFromDatabase(): Promise<Project[]> {
    console.log('🔍 开始从数据库读取项目...');
    const connection = getConnection();
    if (!connection) throw new Error('数据库未连接');
    console.log('✅ 数据库连接获取成功');

    const sql = 'SELECT * FROM projects ORDER BY created_at DESC';
    const [rows] = await connection.execute(sql) as any;
    console.log('📊 数据库查询项目结果行数:', rows.length);
    console.log('📊 数据库原始项目数据:', JSON.stringify(rows, null, 2));
    
    const projects: Project[] = rows.map((row: any) => ({
      id: row.id.toString(),
      name: row.name,
      path: row.path,
      description: row.description,
      createdAt: row.created_at,
      updatedAt: row.updated_at
    }));
    
    console.log('🎯 最终数据库项目数组:', JSON.stringify(projects, null, 2));
    return projects;
  }

  private static async addToDatabase(project: Project): Promise<void> {
    const connection = getConnection();
    if (!connection) throw new Error('数据库未连接');

    const sql = `
      INSERT INTO projects (id, name, path, description, created_at, updated_at) 
      VALUES (?, ?, ?, ?, ?, ?)
    `;
    
    await connection.execute(sql, [
      project.id,
      project.name,
      project.path,
      project.description || null,
      project.createdAt,
      project.updatedAt
    ]);
  }

  private static async deleteFromDatabase(id: string): Promise<void> {
    const connection = getConnection();
    if (!connection) throw new Error('数据库未连接');

    const sql = 'DELETE FROM projects WHERE id = ?';
    await connection.execute(sql, [id]);
  }

  private static async updateInDatabase(id: string, updates: Partial<Omit<Project, 'id' | 'createdAt'>>): Promise<Project | null> {
    const connection = getConnection();
    if (!connection) throw new Error('数据库未连接');

    const updateFields: string[] = [];
    const values: any[] = [];

    if (updates.name !== undefined) {
      updateFields.push('name = ?');
      values.push(updates.name);
    }
    if (updates.path !== undefined) {
      updateFields.push('path = ?');
      values.push(updates.path);
    }
    if (updates.description !== undefined) {
      updateFields.push('description = ?');
      values.push(updates.description);
    }
    
    updateFields.push('updated_at = ?');
    const mysqlDateTime = new Date().toISOString().slice(0, 19).replace('T', ' ');
    values.push(mysqlDateTime);
    values.push(id);

    const sql = `UPDATE projects SET ${updateFields.join(', ')} WHERE id = ?`;
    await connection.execute(sql, values);

    // 返回更新后的项目
    const selectSql = 'SELECT * FROM projects WHERE id = ?';
    const [rows] = await connection.execute(selectSql, [id]) as any;
    
    if (rows.length > 0) {
      const row = rows[0];
      return {
        id: row.id.toString(),
        name: row.name,
        path: row.path,
        description: row.description,
        createdAt: row.created_at,
        updatedAt: row.updated_at
      };
    }
    
    return null;
  }

  // 文件操作方法
  private static async getAllFromFile(): Promise<Project[]> {
    try {
      const data = await fs.readFile(PROJECTS_FILE, 'utf-8');
      return JSON.parse(data);
    } catch (error) {
      return [];
    }
  }

  private static async addToFile(project: Project): Promise<void> {
    try {
      const projects = await this.getAllFromFile();
      projects.push(project);
      
      // 确保目录存在
      await fs.mkdir(path.dirname(PROJECTS_FILE), { recursive: true });
      await fs.writeFile(PROJECTS_FILE, JSON.stringify(projects, null, 2));
    } catch (error) {
      console.error('保存项目到文件失败:', error);
      throw error;
    }
  }

  private static async deleteFromFile(id: string): Promise<void> {
    try {
      const projects = await this.getAllFromFile();
      const filteredProjects = projects.filter(p => p.id !== id);
      await fs.writeFile(PROJECTS_FILE, JSON.stringify(filteredProjects, null, 2));
    } catch (error) {
      console.error('从文件删除项目失败:', error);
      throw error;
    }
  }

  private static async updateInFile(id: string, updates: Partial<Omit<Project, 'id' | 'createdAt'>>): Promise<Project | null> {
    try {
      const projects = await this.getAllFromFile();
      const projectIndex = projects.findIndex(p => p.id === id);
      
      if (projectIndex === -1) {
        return null;
      }
      
      projects[projectIndex] = {
        ...projects[projectIndex],
        ...updates,
        updatedAt: new Date().toISOString()
      };
      
      await fs.writeFile(PROJECTS_FILE, JSON.stringify(projects, null, 2));
      return projects[projectIndex];
    } catch (error) {
      console.error('在文件中更新项目失败:', error);
      throw error;
    }
  }
}